IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncSchedJobScheduleMSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncSchedJobScheduleMSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncSchedJobScheduleMSSQL
     @SrvrInstncId             int,
     @PackageExecutionId       bigint,
     @SrvrInstncSchedJobId     int,
     @ScheduleIdentifier       uniqueidentifier,
     @ScheduleId               int,
     @ScheduleName             varchar(128),
     @ScheduleOriginServerName varchar(128),
     @ScheduleOwnerName        varchar(128),
     @ScheduleEnabledFlag      tinyint,
     @ScheduleCrDt             datetime,
     @ScheduleLastModDt        datetime,
     @ScheduleVersion          int,
     @CodedRawSchedule         varchar(120),
     @freq_type                int,
     @freq_interval            int,
     @freq_subday_type         int,
     @freq_subday_interval     int,
     @freq_relative_interval   int,
     @freq_recurrence_factor   int,
     @active_start_date        int,
     @active_end_date          int,
     @active_start_time        int,
     @active_end_time          int,
     @ScheduleDeletedFlag      tinyint       = 0
AS
/*
 *
 * SaveSrvrInstncSchedJobStepMSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/10/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @StartDt     datetime,
             @PEI         bigint,
             @RC          int,
             @NOW         datetime,
             @SchedDesc   varchar(255)

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Condition Flags and data
     SELECT @ScheduleDeletedFlag = CASE WHEN @ScheduleDeletedFlag != 1 OR @ScheduleDeletedFlag IS NULL THEN 0 ELSE 1 END
     SELECT @SchedDesc = ISNULL( dbo.GetMSSQLJobScheduleDescription( @freq_type, 
                                                                     @freq_interval, 
                                                                     @freq_subday_type, 
                                                                     @freq_subday_interval, 
                                                                     @freq_relative_interval, 
                                                                     @freq_recurrence_factor, 
                                                                     @active_start_date, 
                                                                     @active_end_date, 
                                                                     @active_start_time, 
                                                                     @active_end_time ), 
                                 'Unable to decode schedule' )

     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncSchedJobScheduleMSSQL
                     WHERE  SrvrInstncId         = @SrvrInstncId 
                     AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
                     AND    ScheduleId           = @ScheduleId )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          INSERT dbo.SrvrInstncSchedJobScheduleMSSQL
               ( SrvrInstncId,
                 SrvrInstncSchedJobId,
                 ScheduleId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 PackageExecutionId,
                 ScheduleDeletedFlag,
                 ScheduleIdentifier,
                 ScheduleName,
                 ScheduleOriginServerName,
                 ScheduleOwnerName,
                 ScheduleEnabledFlag,
                 ScheduleDescription,
                 ScheduleCrDt,
                 ScheduleLastModDt,
                 ScheduleVersion,
                 CodedRawSchedule )
          SELECT @SrvrInstncId,
                 @SrvrInstncSchedJobId,
                 @ScheduleId,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @PackageExecutionId,
                 0, -- ScheduleDeletedFlag
                 @ScheduleIdentifier,
                 @ScheduleName,
                 @ScheduleOriginServerName,
                 @ScheduleOwnerName,
                 @ScheduleEnabledFlag,
                 @SchedDesc,
                 @ScheduleCrDt,
                 @ScheduleLastModDt,
                 @ScheduleVersion,
                 @CodedRawSchedule
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncSchedJobStepMSSQL
          WHERE  SrvrInstncId         = @SrvrInstncId
          AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
          AND    StepId               = @ScheduleId
          AND    CurrentRecordFlag    = 1
          ORDER BY StartDt

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncSchedJobStepMSSQL  
          SET    EndDt             = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag = 0
          WHERE  SrvrInstncId         = @SrvrInstncId 
          AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
          AND    StepId               = @ScheduleId
          AND    CurrentRecordFlag    = 1

          IF ( @ScheduleDeletedFlag = 0 )
          BEGIN -- Do regular insert
               INSERT dbo.SrvrInstncSchedJobScheduleMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      ScheduleId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      ScheduleDeletedFlag,
                      ScheduleIdentifier,
                      ScheduleName,
                      ScheduleOriginServerName,
                      ScheduleOwnerName,
                      ScheduleEnabledFlag,
                      ScheduleDescription,
                      ScheduleCrDt,
                      ScheduleLastModDt,
                      ScheduleVersion,
                      CodedRawSchedule )
               SELECT @SrvrInstncId,
                      @SrvrInstncSchedJobId,
                      @ScheduleId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      0, -- ScheduleDeletedFlag
                      @ScheduleIdentifier,
                      @ScheduleName,
                      @ScheduleOriginServerName,
                      @ScheduleOwnerName,
                      @ScheduleEnabledFlag,
                      @SchedDesc,
                      @ScheduleCrDt,
                      @ScheduleLastModDt,
                      @ScheduleVersion,
                      @CodedRawSchedule
               FROM   dbo.SrvrInstncSchedJobScheduleMSSQL
               WHERE  SrvrInstncId         = @SrvrInstncId 
               AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
               AND    ScheduleId           = @ScheduleId
               AND    StartDt              = @StartDt
               -- Decide if there will be an insert
               AND  ( ScheduleDeletedFlag = 1
                      --1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR ( @ScheduleName             != ScheduleName )
                   OR ( @ScheduleOriginServerName != ScheduleOriginServerName )
                   OR ( @ScheduleOwnerName        != ScheduleOwnerName )
                   OR ( @ScheduleEnabledFlag      != ScheduleEnabledFlag )
                   OR ( @SchedDesc                != ScheduleDescription )
                   OR ( @ScheduleCrDt             != ScheduleCrDt )
                   OR ( @CodedRawSchedule != CodedRawSchedule )
                   -- Handle nullable columns
                   OR ( @ScheduleIdentifier       != ScheduleIdentifier
                     OR ( @ScheduleIdentifier IS     NULL AND ScheduleIdentifier IS NOT NULL ) 
                     OR ( @ScheduleIdentifier IS NOT NULL AND ScheduleIdentifier IS     NULL ) )
                   OR ( @ScheduleLastModDt        != ScheduleLastModDt
                     OR ( @ScheduleLastModDt  IS     NULL AND ScheduleLastModDt  IS NOT NULL ) 
                     OR ( @ScheduleLastModDt  IS NOT NULL AND ScheduleLastModDt  IS     NULL ) )
                   OR ( @ScheduleVersion          != ScheduleVersion
                     OR ( @ScheduleVersion    IS     NULL AND ScheduleVersion    IS NOT NULL ) 
                     OR ( @ScheduleVersion    IS NOT NULL AND ScheduleVersion    IS     NULL ) )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               INSERT dbo.SrvrInstncSchedJobScheduleMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      ScheduleId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      ScheduleDeletedFlag,
                      ScheduleIdentifier,
                      ScheduleName,
                      ScheduleOriginServerName,
                      ScheduleOwnerName,
                      ScheduleEnabledFlag,
                      ScheduleDescription,
                      ScheduleCrDt,
                      ScheduleLastModDt,
                      ScheduleVersion,
                      CodedRawSchedule )
               SELECT SrvrInstncId,
                      SrvrInstncSchedJobId,
                      ScheduleId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      1, -- ScheduleDeletedFlag
                      ScheduleIdentifier,
                      ScheduleName,
                      ScheduleOriginServerName,
                      ScheduleOwnerName,
                      ScheduleEnabledFlag,
                      ScheduleDescription,
                      ScheduleCrDt,
                      ScheduleLastModDt,
                      ScheduleVersion,
                      CodedRawSchedule
               FROM   dbo.SrvrInstncSchedJobScheduleMSSQL 
               WHERE  SrvrInstncId         = @SrvrInstncId
               AND    SrvrInstncSchedJobId = @SrvrInstncSchedJobId
               AND    ScheduleId           = @ScheduleId
               AND    StartDt              = @StartDt

               SELECT @RC = @@ROWCOUNT 
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance scheduled job step MS SQL specific record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncSchedJobScheduleMSSQL;
GO
